package com.iweb.dao.impl;

import com.iweb.dao.BaseDao;
import com.iweb.dao.CategoryDao;
import com.iweb.entity.Category;
import com.iweb.util.DBHelper;
import com.iweb.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImpl extends BaseDao implements CategoryDao {

    @Override
    public int selectTotalCount() throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.打开连接
            connection = DBHelper.getConnection();
            // 2.编写sql语句
            String sql = "select count(1) from littlemall_product_category";
            // 3.执行
            preparedStatement = connection.prepareStatement(sql);
            // 执行
            resultSet = preparedStatement.executeQuery();
            // 4.解析结果
            int count = 0;
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
            return count;
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<Category> selectPagedList(Pager<Category> pager) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 保存sql的参数对应的值
        try {
            // 1.打开连接
            connection = DBHelper.getConnection();
            // 2.编写sql语句
            List<Object> params = new ArrayList<>();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT c1.*, c2.name AS parentName FROM `littlemall_product_category` c1 LEFT JOIN littlemall_product_category c2 ON c1.parentId = c2.id where 1=1 ");
            sb.append("limit ?,? ");
            // 添加分页参数值
            params.add(pager.getOffset());
            params.add(pager.getPageSize());

            preparedStatement = connection.prepareStatement(sb.toString());
            // 执行
            resultSet = this.executeQuery(connection, preparedStatement, params.toArray());
            // 4.解析结果
            List<Category> CategoryList = new ArrayList<>();
            Category resultCategory = null;
            while (resultSet.next()) {
                resultCategory = this.parseFullResultSet(resultSet);
                CategoryList.add(resultCategory);
            }
            return CategoryList;
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public Category selectCategoryById(Integer id) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBHelper.getConnection();
            String sql = "select * from littlemall_product_category where id=?";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = this.executeQuery(connection, preparedStatement, id);
            // 解析结果
            Category Category = null;
            if (resultSet.next()) {
                Category = parseResultSet(resultSet);
            }
            return Category;
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<Category> selectList() throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.打开连接
            connection = DBHelper.getConnection();
            // 2.编写sql语句
            String sql = "SELECT c1.*, c2.name AS parentName FROM `littlemall_product_category` c1 LEFT JOIN littlemall_product_category c2 ON c1.parentId = c2.id";
            // 3.执行
            preparedStatement = connection.prepareStatement(sql);
            // 执行
            resultSet = preparedStatement.executeQuery();
            // 4.解析结果
            List<Category> CategoryList = new ArrayList<>();
            Category Category = null;
            while (resultSet.next()) {
                Category = this.parseFullResultSet(resultSet);
                CategoryList.add(Category);
            }
            return CategoryList;
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<Category> selectList(Integer parentId) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.打开连接
            connection = DBHelper.getConnection();
            // 2.编写sql语句
            String sql = "SELECT * FROM littlemall_product_category WHERE parentId=?";
            // 3.执行
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, parentId);
            // 执行
            resultSet = preparedStatement.executeQuery();
            // 4.解析结果
            List<Category> CategoryList = new ArrayList<>();
            Category Category = null;
            while (resultSet.next()) {
                Category = this.parseResultSet(resultSet);
                CategoryList.add(Category);
            }
            return CategoryList;
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public int delete(Integer id) throws Exception {
        String sql = "delete from littlemall_product_category where id=?";
        int ret = this.executeUpdate(sql, id);
        return ret;
    }

    @Override
    public void insert(Category category) throws Exception {
        String sql = "insert into littlemall_product_category values(null,?,?,?,?)";
        // 设置参数
        Object[] params = {
                category.getName(),
                category.getParentId(),
                category.getType(),
                category.getIconClass()
        };
        this.executeUpdate(sql, params);
    }

    @Override
    public int update(Category category) throws Exception {
        String sql = "update littlemall_product_category set CategoryName=?, sex=?, identityCode=?, email=?, mobile=?, type=? where id=?";
        // 设置参数
        Object[] params = {
                category.getName(),
                category.getParentId(),
                category.getType(),
                category.getIconClass(),
                category.getId()
        };
        return this.executeUpdate(sql, params);
    }

    /**
     * 解析ResultSet结果
     *
     * @param resultSet 已经被指向有结果行的resultSet
     * @return
     * @throws SQLException
     */
    private Category parseResultSet(ResultSet resultSet) throws SQLException {
        Category Category = new Category();
        Category.setId(resultSet.getInt("id"));
        Category.setName(resultSet.getString("name"));
        Category.setIconClass(resultSet.getString("iconClass"));
        Category.setParentId(resultSet.getInt("parentId"));
        Category.setType(resultSet.getInt("type"));
        return Category;
    }

    /**
     * 解析ResultSet结果
     *
     * @param resultSet 已经被指向有结果行的resultSet
     * @return
     * @throws SQLException
     */
    private Category parseFullResultSet(ResultSet resultSet) throws SQLException {
        Category Category = new Category();
        Category.setId(resultSet.getInt("id"));
        Category.setName(resultSet.getString("name"));
        Category.setParentName(resultSet.getString("parentName"));
        Category.setIconClass(resultSet.getString("iconClass"));
        Category.setParentId(resultSet.getInt("parentId"));
        Category.setType(resultSet.getInt("type"));
        return Category;
    }
}
